import { BaseModel } from './BaseModel'

export interface CategoryData {
  id?: number
  name: string
  slug: string
  description?: string
  sort_order?: number
  status: 'active' | 'inactive'
  color?: string
  icon?: string
  created_at?: string
  updated_at?: string
  post_count?: number
}

export class Category extends BaseModel {
  protected static tableName = 'categories'

  // 获取所有活跃分类（包含项目数量）
  static async getActiveCategories(): Promise<CategoryData[]> {
    const sql = `
      SELECT 
        c.*,
        COALESCE(COUNT(p.id), 0) as post_count
      FROM categories c
      LEFT JOIN projects p ON c.slug = p.category AND p.status = 'published'
      WHERE c.status = 'active'
      GROUP BY c.id
      ORDER BY c.sort_order ASC, c.name ASC
    `
    const result = await this.query<CategoryData[]>(sql)
    return result[0] || []
  }

  // 获取所有分类（管理后台使用）
  static async getAllCategories(): Promise<CategoryData[]> {
    const sql = `
      SELECT 
        c.*,
        COALESCE(COUNT(p.id), 0) as post_count
      FROM categories c
      LEFT JOIN projects p ON c.slug = p.category
      GROUP BY c.id
      ORDER BY c.created_at DESC
    `
    const result = await this.query<CategoryData[]>(sql)
    return result[0] || []
  }

  // 根据ID获取分类
  static async getById(id: string): Promise<CategoryData | null> {
    const sql = `
      SELECT 
        c.*,
        COALESCE(COUNT(p.id), 0) as post_count
      FROM categories c
      LEFT JOIN projects p ON c.slug = p.category
      WHERE c.id = ?
      GROUP BY c.id
    `
    const result = await this.query<CategoryData[]>(sql, [id])
    const categories = result[0] || []
    return categories.length > 0 ? categories[0] ?? null : null
  }

  // 根据slug获取分类
  static async getBySlug(slug: string): Promise<CategoryData | null> {
    const sql = `
      SELECT 
        c.*,
        COALESCE(COUNT(p.id), 0) as post_count
      FROM categories c
      LEFT JOIN projects p ON c.slug = p.category AND p.status = 'published'
      WHERE c.slug = ? AND c.status = 'active'
      GROUP BY c.id
    `
    const result = await this.query<CategoryData[]>(sql, [slug])
    const categories = result[0] || []
    return categories.length > 0 ? categories[0] ?? null : null
  }

  // 检查slug是否已存在
  static async isSlugExists(slug: string, excludeId?: number): Promise<boolean> {
    let sql = `SELECT COUNT(*) as count FROM categories WHERE slug = ?`
    const params: any[] = [slug]
    
    if (excludeId) {
      sql += ` AND id != ?`
      params.push(excludeId)
    }
    
    const result = await this.query<{count: number}[]>(sql, params)
    return (result[0]?.[0] as {count: number})?.count > 0
  }

  // 创建分类（检查slug唯一性）
  static async createCategory(data: Omit<CategoryData, 'id' | 'created_at' | 'updated_at'>): Promise<number> {
    // 检查slug是否已存在
    if (await this.isSlugExists(data.slug)) {
      throw new Error('分类别名已存在')
    }
    
    try {
      // 直接执行插入操作
      const sql = `
        INSERT INTO ${this.tableName} (name, slug, description, color, icon, status, sort_order)
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `
      const params = [
        data.name,
        data.slug,
        data.description || '',
        data.color || '#1890ff',
        data.icon || 'fa:folder',
        data.status || 'active',
        data.sort_order || 0
      ]
      
      const result = await this.execute(sql, params)
      return result.insertId
    } catch (error: any) {
      console.error('创建分类失败:', error)
      throw new Error(error.message || '创建分类失败')
    }
  }

  // 实现create方法，用于继承自BaseModel
  static async create(data: Omit<CategoryData, 'id' | 'created_at' | 'updated_at'>): Promise<number> {
    return this.createCategory(data)
  }

  // 更新分类（检查slug唯一性）
  static async updateCategory(id: string, data: Partial<CategoryData>): Promise<boolean> {
    // 如果更新slug，检查是否已存在
    if (data.slug && await this.isSlugExists(data.slug, parseInt(id))) {
      throw new Error('分类别名已存在')
    }
    
    const fields = Object.keys(data).filter(field => field !== 'updated_at')
    if (fields.length === 0) {
      return false
    }
    
    const setClause = fields.map(field => `${field} = ?`).join(', ')
    const values = fields.map(field => data[field as keyof CategoryData])
    values.push(id)
    
    // 使用 MySQL 的 NOW() 函数来处理时间戳
    const sql = `UPDATE ${this.tableName} SET ${setClause}, updated_at = NOW() WHERE id = ?`
    
    try {
      const result = await this.execute(sql, values)
      return (result as any)?.affectedRows > 0
    } catch (error) {
      console.error('更新分类失败:', error)
      return false
    }
  }

  // 删除分类
  static async delete(id: string): Promise<boolean> {
    try {
      const sql = `DELETE FROM ${this.tableName} WHERE id = ?`
      const result = await this.execute(sql, [id])
      return (result as any)?.affectedRows > 0
    } catch (error) {
      console.error('删除分类失败:', error)
      return false
    }
  }
}